import pandas as pd
from datetime import datetime, timedelta
import pymysql
import pymysql.cursors


class MysqlUtil(object):
    def __init__(self, *args):
        try:
            self.conn = pymysql.connect(
                host='localhost',
                port=3306,
                user='root',
                passwd='sjk1234',
                db='sys',
                charset='utf8'
            )
            self.cursor = self.conn.cursor(cursor=pymysql.cursors.DictCursor)
        except pymysql.MySQLError as e:
            print(f"数据库连接错误: {e}")
            raise

    def get_scenic_data(self):
        sql = """
        SELECT LEFT(u.id_no,4) as city_code, DATE_FORMAT(o.create_time, '%Y-%m') as month, COUNT(u.id) as visitor_count
        FROM ticket_order o join ticket_order_user_rel u on u.order_id = o.id
        WHERE
        LENGTH(u.id_no) > 10 and o.pay_time is not null and o.pay_time != ''
        GROUP BY city_code, DATE_FORMAT(o.create_time, '%Y-%m');
        """
        try:
            self.cursor.execute(sql)
            ret = self.cursor.fetchall()
            result = []
            CITY_DICT = {
                "110000": "北京市",
                "310000": "上海市",
                # 可以根据实际情况添加更多城市代码和名称的映射
            }
            for row in ret:
                city_code = row['city_code']
                city_name = CITY_DICT.get(city_code, "未知城市")
                result.append({
                    "city_code": city_code,
                   'month': row['month'],
                    "visitor_count": row['visitor_count'],
                    "city_name": city_name
                })
            return pd.DataFrame(result)
        except pymysql.MySQLError as e:
            print(f"查询数据时发生错误: {e}")
            raise
        finally:
            self.close()

    def close(self):
        try:
            self.cursor.close()
            self.conn.close()
        except pymysql.MySQLError as e:
            print(f"关闭数据库连接时发生错误: {e}")


def calculate_baseline(df, current_month, window_size=6):
    df_current = df[df['month'] == current_month]
    history_start = current_month - pd.DateOffset(months=window_size)
    df_history = df[(df['month'] >= history_start) & (df['month'] < current_month)]
    df_baseline = df_history.groupby('city_name')['visitor_count'].agg(['mean', 'std']).reset_index()
    df_baseline.rename(columns={'mean': 'hist_mean', 'std': 'hist_std'}, inplace=True)
    return df_baseline


if __name__ == "__main__":
    try:
        mu = MysqlUtil()
        df_city_monthly = mu.get_scenic_data()
        # 明确指定日期时间格式
        df_city_monthly['month'] = pd.to_datetime(df_city_monthly['month'], format='%Y-%m')
        current_month = pd.to_datetime('2024-12-01')
        df_baseline = calculate_baseline(df_city_monthly, current_month)

        # 合并当前月数据
        df_merged = df_city_monthly.merge(df_baseline, on='city_name', how='left')

        # 计算z-score
        df_merged['z_score'] = (df_merged['visitor_count'] - df_merged['hist_mean']) / df_merged['hist_std']

        # 标记爆增爆跌的城市(z_score > 3 or z_score < -3)
        df_increased = df_merged[df_merged['z_score'] > 3]
        df_reduce = df_merged[df_merged['z_score'] < -3]

        print(df_increased)
        print('----------------------')
        print(df_reduce)
    except Exception as e:
        print(f"程序执行过程中发生错误: {e}")